﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using Follow.Utils;
using Follow.Model;
using System.Transactions;


namespace Follow.DataAccess
{
    public class GrupoDAO
    {
        private SqlDataReader reader = null;
        private SqlConnection conn = null;
        private SqlCommand cmd = null;

        public GrupoDAO()
        {
            conn = ConnectionUtils.getConnection();
        }

        public IList<Grupo> findAll(string descricao)
        {
            IList<Grupo> Grupos = new List<Grupo>();
            try
            {
                conn.Open();
                String sqlCommand = " select * from grupo where descricao like '%" + descricao + "%' order by descricao ";
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Grupo grp = new Grupo();
                    grp.IDGrupo = reader.GetInt32(0);
                    grp.Descricao = reader.GetString(1);
                    Grupos.Add(grp);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Dispose();
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
            return Grupos;

        }

        public IList<Grupo> insert(Grupo grupo, int idUsuario)
        {
            String sqlCommand;
            IList<Grupo> Grupos = new List<Grupo>();

            try
            {
                conn.Open();
                sqlCommand = "insert into grupo (descricao) values ('" + grupo.Descricao + "'); ";
                sqlCommand += "select * from grupo where idGrupo=@@IDENTITY;";

                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Grupo grp = new Grupo();
                    grp.IDGrupo = reader.GetInt32(0);
                    grp.Descricao = reader.GetString(1);
                    Grupos.Add(grp);
                }

            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }
            LogDAO log = new LogDAO();
            log.insert(idUsuario, sqlCommand, "Insert", "Grupos");
            return Grupos;
        }

        public IList<Grupo> update(Grupo grupo, int idUsuario)
        {
            String sqlCommand;
            IList<Grupo> Grupos = new List<Grupo>();

            try
            {
                conn.Open();
                sqlCommand = " update grupo set descricao = '" + grupo.Descricao + "' where idgrupo = " + grupo.IDGrupo + "; ";
                sqlCommand += "delete from grupofunc where grupo_idGrupo = " + grupo.IDGrupo + "; ";
                sqlCommand += "select * from grupo where idGrupo = " + grupo.IDGrupo;

                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Grupo grp = new Grupo();
                    grp.IDGrupo = reader.GetInt32(0);
                    grp.Descricao = reader.GetString(1);
                    Grupos.Add(grp);
                }

            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (conn != null)
                {
                    conn.Close();
                }
            }
            LogDAO log = new LogDAO();
            log.insert(idUsuario, sqlCommand, "Update", "Grupos");
            return Grupos;
        }

        public void delete(Int32 id, int idUsuario)
        {
            String sqlCommand;
            using (var transaction = new TransactionScope())
            {
                try
                {
                    conn.Open();
                    sqlCommand = "delete from grupofunc where grupo_idGrupo =" + id + "; ";
                    sqlCommand += " delete from grupo where idgrupo =" + id;
                    cmd = new SqlCommand(sqlCommand, conn);
                    cmd.ExecuteNonQuery();
                    transaction.Complete();
                }
                catch (Exception)
                {
                    transaction.Dispose();
                    throw;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
            LogDAO log = new LogDAO();
            log.insert(idUsuario, sqlCommand, "Delete", "Grupos");
        }

        public Grupo findByID(Int32 id)
        {
            Grupo grupos = null;
            try
            {
                conn.Open();
                String sqlCommand = " select * from grupo where idgrupo = " + id;
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    grupos = new Grupo();
                    grupos.IDGrupo = reader.GetInt32(0);
                    grupos.Descricao = reader.GetString(1);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }
            return grupos;
        }

        public Int32 findIdClientes()
        {
            Int32 id = 0;
            try
            {
                conn.Open();
                String sqlCommand = " select idgrupo from grupo where " +
                                    " descricao like 'Cliente' or " +
                                    " descricao like 'Clientes';";
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    id = reader.GetInt32(0);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }
            return id;
        }
    }
}